package com.base.springboot.starter.common.util;

import cn.hutool.core.collection.CollectionUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * Excel导出工具类
 * 
 * @author admin
 * @date 2020/8/17 下午3:48
 */
@Slf4j
public class MyExcelExport {
    /**
     * 显示的导出表的标题
     */
    private String title;
    /**
     * 导出表的列名
     */
    private String[] rowName;

    private List<Object[]> dataList = new ArrayList<>();

    HttpServletResponse response;

    /**
     * 构造方法，传入要导出的数据
     *
     * @param title
     * @param rowName
     * @param dataList2
     * @param response
     */
    public MyExcelExport(String title, String[] rowName, List<Object[]> dataList2, HttpServletResponse response) {
        this.dataList = dataList2;
        this.rowName = rowName;
        this.title = title;
        this.response = response;
    }

    /**
     * 导出数据
     *
     * @throws Exception
     */
    public void export() {
        try {
            // 创建工作簿对象
            HSSFWorkbook workbook = new HSSFWorkbook();
            int sheetNumber = 0;
            if (CollectionUtil.isNotEmpty(dataList)) {
                sheetNumber = dataList.size() / 10000;
            }
            for (int k = 0; k <= sheetNumber; k++) {
                // 创建工作表
                HSSFSheet sheet = workbook.createSheet(title + "-" + (k + 1));
                sheet.autoSizeColumn(k, true);
                // 产生表格标题行
                HSSFRow rowm = sheet.createRow(0);
                HSSFCell cellTiltle = rowm.createCell(0);

                // sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 -
                // 可扩展】
                // 获取列头样式对象
                HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
                // 单元格样式对象
                HSSFCellStyle style = this.getStyle(workbook);

                sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length - 1)));
                cellTiltle.setCellStyle(columnTopStyle);
                cellTiltle.setCellValue(title);

                // 定义所需列数
                int columnNum = rowName.length;
                // 在索引2的位置创建行(最顶端的行开始的第二行)
                HSSFRow rowRowName = sheet.createRow(2);

                // 将列头设置到sheet的单元格中
                for (int n = 0; n < columnNum; n++) {
                    // 创建列头对应个数的单元格
                    HSSFCell cellRowName = rowRowName.createCell(n);
                    // 设置列头单元格的数据类型
                    cellRowName.setCellType(CellType.STRING);
                    HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
                    // 设置列头单元格的值
                    cellRowName.setCellValue(text);
                    // 设置列头单元格样式
                    cellRowName.setCellStyle(columnTopStyle);
                }

                // 将查询出的数据设置到sheet对应的单元格中
                List<Object[]> spiltData = dataList.subList(k * 10000, (k + 1) * 10000 > dataList.size() ? dataList.size() : (k + 1) * 10000);
                for (int i = 0; i < spiltData.size(); i++) {
                    // 遍历每个对象
                    Object[] obj = spiltData.get(i);
                    // 创建所需的行数
                    HSSFRow row = sheet.createRow(i + 3);

                    for (int j = 0; j < obj.length; j++) {
                        // 设置单元格的数据类型
                        HSSFCell cell = null;
                        cell = row.createCell(j, CellType.STRING);
                        // 设置单元格的值
                        if (!"".equals(obj[j]) && obj[j] != null) {
                            cell.setCellValue(obj[j].toString());
                        } else {
                            cell.setCellValue("");
                        }
                        // 设置单元格样式
                        cell.setCellStyle(style);
                    }
                }

                // 让列宽随着导出的列长自动适应
                for (int colNum = 0; colNum < columnNum; colNum++) {
                    int columnWidth = sheet.getColumnWidth(colNum) / 256;
                    for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                        HSSFRow currentRow;
                        // 当前行未被使用过
                        if (sheet.getRow(rowNum) == null) {
                            currentRow = sheet.createRow(rowNum);
                        } else {
                            currentRow = sheet.getRow(rowNum);
                        }
                        if (currentRow.getCell(colNum) != null) {
                            HSSFCell currentCell = currentRow.getCell(colNum);
                            if (currentCell.getCellType() == CellType.STRING) {
                                int length = 100;
                                if (null != currentCell.getStringCellValue()) {
                                    length = currentCell.getStringCellValue().getBytes().length;
                                }
                                if (columnWidth < length) {
                                    columnWidth = length;
                                }
                                if (columnWidth > 251) {
                                    columnWidth = 251;
                                    if (currentCell.getStringCellValue().length() > 83) {
                                        String str = currentCell.getStringCellValue().replace("\r", "").replace("\n", "");
                                        StringBuilder newStr = new StringBuilder();
                                        int end = 0;
                                        for (int i = 0; i < str.length() / 83; i++) {
                                            end = (i + 1) * 83;
											newStr.append(str.substring(i * 83, end));
                                            if (end < str.length()) {
                                                newStr.append("\r\n");
                                            }
                                        }
                                        if (end < str.length()) {
											newStr.append(str.substring(end));
                                        }
                                        currentCell.setCellValue(newStr.toString());
                                    }
                                }
                            }
                        }
                    }
                    sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
                }
            }

            if (workbook != null) {
				String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
				String headStr = "attachment; filename=\"" + fileName + "\"";
				response.setContentType("APPLICATION/OCTET-STREAM");
				response.setHeader("Content-Disposition", headStr);
				OutputStream out = response.getOutputStream();
				workbook.write(out);
            }
        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }

    /**
     * 列头单元格样式
     *
     * @param workbook
     * @return
     */
    public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {

        // 设置字体
        HSSFFont font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 11);
        // 字体加粗
        font.setBold(true);
        // 设置字体名字
        font.setFontName(Fields.FONT_NAME);
        HSSFCellStyle style = workbook.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setFont(font);
        style.setWrapText(false);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        return style;

    }

    /**
     * 列数据信息单元格样式
     *
     * @param workbook
     * @return
     */
    public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        HSSFFont font = workbook.createFont();
        font.setFontName(Fields.FONT_NAME);
        HSSFCellStyle style = workbook.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setFont(font);
        style.setWrapText(false);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFDataFormat format = workbook.createDataFormat();
        style.setDataFormat(format.getFormat("@"));
        return style;
    }

    public static final class Fields {
    	private Fields () {}
    	public static final String FONT_NAME = "Courier New";
	}
}